package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import org.apache.log4j.Logger;

public class BatchSupport {
	protected static Logger logger = Logger.getLogger( BatchSupport.class );
	public static final String  WORKSTATION_OR_PROCESS_CONDITION = "workstation_condition";
	public static final String  PART_NUMBER_OR_FAMILY_CONDITION = " part_number_or_family_id ";
	public static final String  BEGIN_TIME= "begin_time";
	public static final String  END_TIME = "end_time";
	public static final String  LAST_GROUP_TIME = "last_group_time";
	public static final String  BATCH_SERIAL_NUMBER = "batch_serial_number";
	public static final String  NET_USER_ID = "net_user_id";
	
//	public static final String  
	//1.Query pass AND fail AND 63 status
	public static final String  FETCH_BATHCH_SERIAL_TEMPLATE =
		" SELECT distinct s.\"serial-key\" , s.serial_number, s.numcomps, a.action, to_timestamp(a.\"a_DateTime\") as to_timestamp  , s.quantity , s.module_SELECT	" +
		" FROM   activity a, serial s, location l, part p	  " +
		" WHERE  a.\"serial-key\"=s.\"serial-key\"	" +
		"	AND a.\"location-key\"=l.\"location-key\"	" +
		"	AND a.\"part-key\"=p.\"part-key\"   AND s.\"part-key\"=p.\"part-key\"	" +
		"  and  a.anet_userid=s.anet_userid and a.aactive=s.aactive and a.alot=s.alot  " +
		"  and  a.anet_userid=l.anet_userid  and a.aactive=l.aactive and a.alot=l.alot  " +
		"  and  a.anet_userid=p.anet_userid and a.aactive=p.aactive  and a.alot=p.alot  " +
		"  and  s.anet_userid=p.anet_userid and s.aactive=p.aactive  and s.alot=p.alot  " +
		"	AND ( s.module_SELECT=7 OR s.module_SELECT=8 )	" +
		"	AND a.action=11	" +
//		"	AND a.\"a_DateTime\"> (SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ BEGIN_TIME +"')))))" +
//		"	AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"')))))" +
			LAST_GROUP_TIME+
			WORKSTATION_OR_PROCESS_CONDITION  +
//			--and p."family_id"=''		--and p.part_number=''
			PART_NUMBER_OR_FAMILY_CONDITION  +
//		"	AND l.workstation='" +WORKSTATION_CONDITION+"'" +
		"	and a.aactive=0  AND a.anet_userid='"+NET_USER_ID+"'	" +
			
		" union all	" +
	
		" SELECT distinct s.\"serial-key\", s.serial_number, s.numcomps, a.action, to_timestamp(a.\"a_DateTime\") , s.quantity , s.module_SELECT	" +
		" FROM activity a, serial s, location l	,part p " +
		" WHERE a.\"serial-key\"=s.\"serial-key\"	" +
		"	AND a.\"location-key\"=l.\"location-key\"" +
		"	AND a.\"part-key\"=p.\"part-key\"   AND  s.\"part-key\"=p.\"part-key\"	" +
		"  and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot  " +
		"  and  a.anet_userid=l.anet_userid and a.aactive=l.aactive  and a.alot=l.alot  " +
		"  and  a.anet_userid=p.anet_userid and a.aactive=p.aactive  and a.alot=p.alot  " +
		"  and  s.anet_userid=p.anet_userid and s.aactive=p.aactive  and s.alot=p.alot  " +
		"	AND ( s.module_SELECT=7	OR s.module_SELECT=8 ) " +
		"	AND a.action=12	" +
//		"	AND a.\"a_DateTime\"> (SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ BEGIN_TIME +"')))))" +
//		"	AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"')))))" +
			LAST_GROUP_TIME+
//		"	AND l.workstation='C271 Final Test'	" +
//		"	AND l.workstation='" +WORKSTATION_CONDITION+"'" +
			WORKSTATION_OR_PROCESS_CONDITION  +
			PART_NUMBER_OR_FAMILY_CONDITION  +
		"	and a.aactive=0  AND a.anet_userid='"+NET_USER_ID+"'	" +
		"  union all	" +
		
		" SELECT distinct s.\"serial-key\", s.serial_number, s.numcomps, a.action, to_timestamp(max(a.\"a_DateTime\")) , 0 as quantity , 7 as module_SELECT  " +
		" FROM activity a, serial s, location l,part p " +
		" WHERE a.\"serial-key\"=s.\"serial-key\" " +
		"	AND a.\"location-key\"=l.\"location-key\" " +
		"	AND a.\"part-key\"=p.\"part-key\"   AND s.\"part-key\"=p.\"part-key\"	" +
		"  and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot  " +
		"  and  a.anet_userid=l.anet_userid and a.aactive=l.aactive  and a.alot=l.alot  " +
		"  and  a.anet_userid=p.anet_userid and a.aactive=p.aactive  and a.alot=p.alot  " +
		"  and  s.anet_userid=p.anet_userid and s.aactive=p.aactive  and s.alot=p.alot  " +
		"	AND (s.module_SELECT=7 OR  s.module_SELECT=8 ) " +
		"	AND s.\"unit_status-key\"=63 " +
		"	AND a.action=33	" +
//		"	AND a.\"a_DateTime\">( SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ BEGIN_TIME +"')))) )	" +
//		"	AND a.\"a_DateTime\"<= (SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"')))) )	" +
			LAST_GROUP_TIME+
//		"	AND l.workstation='C271 Final Test'	" +
//		"	AND l.workstation='" +WORKSTATION_CONDITION+"'" +
			WORKSTATION_OR_PROCESS_CONDITION  +
//			
			PART_NUMBER_OR_FAMILY_CONDITION  +
		"	AND a.anet_userid='"+NET_USER_ID+"'	" +
			
		" 	and a.aactive=0 GROUP BY s.\"serial-key\", s.serial_number, s.numcomps, a.action  ORDER BY to_timestamp desc	" ;

	//		"--(step 2 AND 3 for action=12 or serial_status=63)" +
					
		
		//--2.Query batch serial defect qty
//	--AND a.\"a_DateTime\">=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('min', timestamp '2010-12-03 00:00:00')))))
	public static final String FETCH_ONE_BATCH_SERIAL_QTY_TEMPLATE = 
		"	SELECT distinct s.\"serial-key\", s.serial_number,a.action, sd.defectcode, sd.defectqty, to_timestamp(a.\"a_DateTime\") " +
		"	FROM activity a, serdef sd, serial s, location l,part p	" +
		"	WHERE a.\"serial-key\"=s.\"serial-key\"	" +
		"	AND a.iparam1=sd.defectseq" +
		"	AND sd.\"serial-key\"=s.\"serial-key\"	" +
		"	AND a.\"location-key\"=l.\"location-key\"	" +
		"	AND a.\"part-key\"=p.\"part-key\"   AND s.\"part-key\"=p.\"part-key\"	" +
		"  and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot " +
		"  and  a.anet_userid=l.anet_userid and a.aactive=l.aactive  and a.alot=l.alot " +
		"  and  a.anet_userid=p.anet_userid and a.aactive=p.aactive  and a.alot=p.alot " +
		"  and  sd.anet_userid=s.anet_userid and sd.aactive=s.aactive  and sd.alot=s.alot " +
		"  and  p.anet_userid=s.anet_userid and p.aactive=s.aactive  and p.alot=s.alot " +
		"	AND sd.repaired=0	" +
		"	AND ( s.module_SELECT=7 OR  s.module_SELECT=8 ) " +
		"	AND a.action=41	" +
		"	AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"'))))) " +
//		"	AND l.workstation='C271 Final Test'	" +
//		"	AND l.workstation='" +WORKSTATION_OR_PROCESS_CONDITION+"'" +
			WORKSTATION_OR_PROCESS_CONDITION  +
		"	AND s.serial_number='"+BATCH_SERIAL_NUMBER+"'	" +
		
		"	AND a.anet_userid='"+NET_USER_ID+"'	" +
		" 	and a.aactive=0 ORDER BY  a.\"a_DateTime\", a.\"activity-key\"  ASC";

	
	//	--3.Query total units by batch
	public static final String FECTH_UNIT_QTY_IN_BATHCH_EACH_QTY_TEMPLATE =
	"	SELECT distinct a.\"serial-key\"	,a.action, count(\"activity-key\")" +
	" 	FROM activity a, serial s 	 " +
	"	WHERE a.\"serial-key\"=s.\"serial-key\"	" +
	"  and a.aactive=s.aactive and  a.anet_userid=s.anet_userid and a.alot=s.alot " +
	"	AND a.action=31" +
	"	AND a.\"a_DateTime\"<=( SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"')))) )" +
	"	AND serial_number='"+BATCH_SERIAL_NUMBER+"'	" +
	"	AND a.anet_userid='"+NET_USER_ID+"'	" +
	"	and a.aactive=0 group by a.action	" +
	
	"	union all	" +
	
	"	SELECT distinct(a.\"serial-key\"),a.action, count(\"activity-key\") " +
	"	FROM activity a, serial s " +
	"	WHERE	 a.\"serial-key\"=s.\"serial-key\"	" +
	"  and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot " +
	"	AND a.action=33	" +
	"	AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+ END_TIME +"')))))" +
	"	AND serial_number='"+BATCH_SERIAL_NUMBER+"'" +
	"	AND a.anet_userid='"+NET_USER_ID+"'	" +
	"	and a.aactive=0 GROUP BY  a.action" ;
	
	
	public static final String FETCH_ONE_BATCH_FAIL_TEMPLATE = 
		" SELECT COUNT(*) FROM " +
		" (" +
		"  SELECT distinct a.iparam1 " +
		"  FROM activity a, component c, serial s " +
		"  WHERE a.\"serial-key\"=s.\"serial-key\" " +
		    " AND c.\"serial-key\"=s.\"serial-key\" " +
		    " and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot " +
			" and  c.anet_userid=s.anet_userid and c.aactive=s.aactive  and c.alot=s.alot " +
			" AND a.iparam1=c.component_number " +
			" AND a.action=31" +
			" AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+END_TIME+"'))))) " +
			" AND s.serial_number='"+BATCH_SERIAL_NUMBER+"'" +
			" AND a.anet_userid='"+NET_USER_ID+"'	" +
			" AND a.aactive=0	" +
		" )  si" +
		" WHERE si.iparam1 not in " +
		" (" +
		"    SELECT distinct a.iparam1 " +
		   " FROM activity a, component c, serial s " +
		   " WHERE a.\"serial-key\"=s.\"serial-key\" " +
    	   "  AND c.\"serial-key\"=s.\"serial-key\" " +
    	   " and  a.anet_userid=s.anet_userid and a.aactive=s.aactive  and a.alot=s.alot " +
			" and  c.anet_userid=s.anet_userid and c.aactive=s.aactive  and c.alot=s.alot " +
			" AND a.iparam1=c.component_number " +
			" AND a.action=33" +
			" AND a.\"a_DateTime\"<=(SELECT INT4(EXTRACT(EPOCH FROM (SELECT date_trunc('second', timestamp '"+END_TIME+"'))))) " +
			" AND s.serial_number='"+BATCH_SERIAL_NUMBER+"'" +
			" AND a.anet_userid='"+NET_USER_ID+"'	" +
			" AND a.aactive=0	" +
		" ) and aactive=0";
		
	
	/**
	 *
	 *  serial-key	serial_number	numcomps	action		to_timestamp
	 * 	1102379		"6A2011RP-02"	51			11			"2010-12-03 01:30:10+08"
	 *	1100678		"6A2011RP-01"	78			33			"2010-12-03 19:00:08+08"
	 *   
	 */
	public static String getBatchSerialSQL(String workstation,String partCondition,String lastGroupTimeCondition,String netUserId){
		String sql= FETCH_BATHCH_SERIAL_TEMPLATE;
		sql = sql.replaceAll(WORKSTATION_OR_PROCESS_CONDITION, workstation);
		sql = sql.replaceAll(PART_NUMBER_OR_FAMILY_CONDITION, partCondition);
		sql = sql.replaceAll(LAST_GROUP_TIME, lastGroupTimeCondition);
//		sql = sql.replaceAll(BEGIN_TIME, beginTime);
//		sql = sql.replaceAll(END_TIME, endTime);
		sql = sql.replaceAll(NET_USER_ID, netUserId);
		return sql;
	}

 
	/**
	 * 鑾峰彇涓�釜batch鐨刦ail 娆℃暟
	 * @param workstation
	 * @param batchSerial
	 * @param endTime
	 * @return
	 */
	public static String getOneBatchFailProcessNumbers(String workstation,String batchSerial,String endTime,String netUserId){
		String sql= FETCH_ONE_BATCH_SERIAL_QTY_TEMPLATE;
		sql = sql.replaceAll(WORKSTATION_OR_PROCESS_CONDITION, workstation);
		sql = sql.replaceAll(BATCH_SERIAL_NUMBER, batchSerial);
//		sql = sql.replaceAll(BEGIN_TIME, beginTime);
		sql = sql.replaceAll(END_TIME, endTime);
		sql = sql.replaceAll(NET_USER_ID, netUserId);
		return sql;
	}
	
	public static String getUnitsBatchQtySQL(String workstation,String batchSerial,String endTime,String netUserId){
		String sql= FECTH_UNIT_QTY_IN_BATHCH_EACH_QTY_TEMPLATE;
		sql = sql.replaceAll(WORKSTATION_OR_PROCESS_CONDITION, workstation);
		sql = sql.replaceAll(BATCH_SERIAL_NUMBER, batchSerial);
//		sql = sql.replaceAll(BEGIN_TIME, beginTime);
		sql = sql.replaceAll(END_TIME, endTime);
		sql = sql.replaceAll(NET_USER_ID, netUserId);
		return sql;
	}
	
	public static String getOneBatchHasFailUnitAccount(String batchSerial,String endTime,String netUserId){
		String sql= FETCH_ONE_BATCH_FAIL_TEMPLATE;
//		sql = sql.replaceAll(WORKSTATION_OR_PROCESS_CONDITION, workstation);
		sql = sql.replaceAll(BATCH_SERIAL_NUMBER, batchSerial);
//		sql = sql.replaceAll(BEGIN_TIME, beginTime);
		sql = sql.replaceAll(END_TIME, endTime);
		sql = sql.replaceAll(NET_USER_ID, netUserId);
		return sql;
	}
	
	public static void main(String args[]){
		String workstation = " AND l.workstation='C271 Final Test' ";
//		logger.info( BatchSupport.getBatchSerialSQL(workstation,"", "2010-12-03 00:00:00", "2010-12-03 19:01:0",""));
		logger.info("=============================================================");
		logger.info( BatchSupport.getOneBatchFailProcessNumbers(workstation, "6A2011RP-01", "2010-12-03 19:00:0",""));
		logger.info("=============================================================");
		logger.info( BatchSupport.getUnitsBatchQtySQL(workstation, "6A2011RP-01", "2010-12-03 18:59:60",""));
		logger.info("=============================================================");
		logger.info( BatchSupport.getOneBatchHasFailUnitAccount("6A2011RP-01", "2010-12-03 18:59:60",""));
	}
}
